
DROP PROCEDURE IF EXISTS candidate_filter_major;

DELIMITER //
CREATE PROCEDURE `candidate_filter_major` (
    IN eduMajor INT, IN eduMajorImp INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER

BEGIN

    -- MAXIMUM SCORE IS 10 AND MINIMUM SCORE IS 0
    IF eduMajorImp = 10 THEN
        SELECT
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            10 AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_education AS canEdu
        WHERE
            can.is_deleted = 0
            AND canEdu.candidate_id = can.id
            AND canEdu.major_id = eduMajor;
    ELSE
        SELECT  
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            eduMajorImp AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_education AS canEdu
        WHERE
            can.is_deleted = 0
            AND canEdu.candidate_id = can.id
            AND canEdu.major_id = eduMajor;
    END IF;
END//

